﻿package com.sny.tang.readex;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.sny.tang.readex.Order;

/**
 * 
 * @author tyler.tang ty_sany@163.com 师爷
 * 
 */
public class ReadEx {

	public static final String OUT_FILE_NAME = "FINAL_FILE.xlsx";
	private static ArrayList oder = new ArrayList();
	// 我的数据
	private static ArrayList myOrders = new ArrayList();
	// 来自eub的数据
	private static ArrayList eubOrders = new ArrayList();
	// 来自eub的数据
	private static ArrayList resultOrders = new ArrayList();
	// 编号池
	private static ArrayList pools = new ArrayList();

	public static void main(String args[]) throws BiffException, IOException {

		String file_my = "";
		// String file_eub = args[1];

		InputStream is_my = readFile("F://MY_06.xls");
		InputStream is_eub = readFile("F://EUB_06.xls");

		readMyOrders(is_my);
		readEubOrders(is_eub);

		writeFile(myOrders, eubOrders);

	}

	public static void readMyOrders(InputStream is) throws BiffException, IOException {

		Workbook workBook = Workbook.getWorkbook(is);
		Sheet sheet = workBook.getSheet(0);

		int Rows = sheet.getRows();

		for (int i = 0; i < Rows; i++) {

			Order order = new Order();

			Cell numberCell = sheet.getCell(1, i);
			Cell weightCell = sheet.getCell(2, i);

			String number = numberCell.getContents();
			String weight = weightCell.getContents();

			if (number == null || weight.equals("")) {
				continue;
			}

			if (weight == null || weight.equals("")) {
				weight = "0";
			}

			int weightInt = Integer.parseInt(weight);

			order.setId(number);
			order.setWeight(weightInt);

			myOrders.add(order);
		}

	}

	/**
	 * @param is
	 * @throws BiffException
	 * @throws IOException
	 */
	public static void readEubOrders(InputStream is) throws BiffException, IOException {

		Workbook workBook = Workbook.getWorkbook(is);
		Sheet sheet = workBook.getSheet(0);

		int Rows = sheet.getRows();

		for (int i = 0; i < Rows; i++) {

			Order order = new Order();

			Cell numberCell = sheet.getCell(2, i);
			Cell weightCell = sheet.getCell(6, i);

			String number = numberCell.getContents();
			String weight = weightCell.getContents();

			if (number == null || weight.equals("")) {
				continue;
			}

			if (weight == null || weight.equals("")) {
				weight = "0";
			}

			// 转化异常时认为为非数值
			int weightInt = 0;
			try {
				weightInt = (int)Float.parseFloat(weight);
			} catch (NumberFormatException e) {
				continue;
			}

			order.setId(number);
			order.setWeight(weightInt);
			eubOrders.add(order);
		}
	}

	/**
	 * 读取信息流
	 * 
	 * @param filePath
	 * @return
	 */
	public static InputStream readFile(String filePath) {

		try {
			InputStream is = new FileInputStream(filePath);

			return is;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 写入到文件中 格式为
	 * <li>自己编号
	 * <li>EUB编号
	 * <li>自己重量
	 * <li>EUB重量
	 * <li>EUB折扣后重量
	 * <Li>折扣后相差的重量
	 * 
	 * @param myOrders
	 * @param eubOrders
	 */
	public static void writeFile(ArrayList myOrders, ArrayList eubOrders) {

		// 完成初始化所有的ID编号
		initNumPool(myOrders, eubOrders);

		int length = pools.size();

		// 以Id组为标准找两组数据
		for (int i = 0; i < length; i++) {

			Order rsOrder = new Order();

			String id = (String) pools.get(i);

			Order myOrder = null;
			Order eubOrder = null;

			myOrder = findOrder(myOrders, id);
			eubOrder = findOrder(eubOrders, id);

			rsOrder.setId(id);

			if (myOrder != null) {
				rsOrder.setRs_self_id(myOrder.getId());
				rsOrder.setRs_self_weigt(myOrder.getWeight());
			}

			if (eubOrder != null) {
				rsOrder.setRs_eub_id(eubOrder.getId());
				rsOrder.setRs_eub_weight(eubOrder.getWeight());
			}

			if (myOrder != null && eubOrder != null) {
				// float eubRateWeight = eubOrder.getWeight()*0.85f;
				// float selfWeight = myOrder.getWeight();
				rsOrder.setRs_weight(myOrder.getWeight() - eubOrder.getWeight());

				System.out.println("id:\t" + id);
				System.out.println(myOrder.toString());
				System.out.println(eubOrder.toString());
			}
			System.out.println(rsOrder.showRsInfo());
			System.out.println("------------------------------------------");
			resultOrders.add(rsOrder);
		}

		initWriteFile(resultOrders);

	}

	private static void initWriteFile(ArrayList resultOrders) {

		File file = new File("F://RS_06.xls");

		if (!file.exists()) {
			file.mkdirs();
		}
		try {
			OutputStream os = new FileOutputStream(file);
			createExcel(os, resultOrders);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 找订单
	 * 
	 * @param orders
	 * @param id
	 * @return
	 */
	private static Order findOrder(ArrayList orders, String id) {
		Order order = null;

		for (int i = 0; i < orders.size(); i++) {

			Order orderT = (Order) orders.get(i);
			String num = (String) orderT.getId();

			if (num != null && num.equals(id)) {
				return (Order) orders.get(i);
			}
		}
		return order;
	}

	/**
	 * 初始化编号
	 * 
	 * @param myOrders
	 * @param eubOrders
	 */
	public static void initNumPool(ArrayList myOrders, ArrayList eubOrders) {

		for (int i = 0; i < myOrders.size(); i++) {
			Order order = (Order) myOrders.get(i);
			String id = (String) order.getId();
			isExist((id));
		}
		for (int i = 0; i < eubOrders.size(); i++) {
			Order order = (Order) eubOrders.get(i);
			String id = (String) order.getId();
			isExist((id));
		}
	}

	private static boolean isExist(String id) {
		boolean result = pools.contains(id);
		if (!result) {
			pools.add(id);
		}
		return result;
	}

	public static void createExcel(OutputStream os, ArrayList orders) throws WriteException, IOException {

		// 创建工作薄
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		// 创建新的一页
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		// 创建要显示的具体内容
		WritableFont color = new WritableFont(WritableFont.ARIAL);// 选择字体
		color.setColour(Colour.GOLD);// 设置字体颜色为金黄色

		Label floats = new Label(0, 0, "编号");
		sheet.addCell(floats);
		Label integers = new Label(1, 0, "自重");
		sheet.addCell(integers);
		Label booleans = new Label(2, 0, "EUB重");
		sheet.addCell(booleans);
		Label dates = new Label(3, 0, "差值");
		sheet.addCell(dates);

		WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.RED);
		WritableCellFormat wcfFC = new WritableCellFormat(wfc);

		for (int i = 0; i < orders.size(); i++) {

			Order order = (Order) orders.get(i);

			String number = order.getId();
			float selfWeight = order.getRs_self_weigt();
			float eubWeight = order.getRs_eub_weight();
			float rsWeight = order.getRs_weight();

			// 编号
			Label numLabel = new Label(0, i + 1, number);
			sheet.addCell(numLabel);

			// 自重
			WritableFont boldNumber = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);// 黑体
			WritableCellFormat boldNumberFormate = new WritableCellFormat(boldNumber);
			Number ints = new Number(1, i + 1, selfWeight, boldNumberFormate);

			sheet.addCell(ints);

			// EUB重
			Number eubNum = new Number(2, i + 1, eubWeight);
			sheet.addCell(eubNum);

			// 差值
			Number rsWeightNum = null;

			if (rsWeight < 0) {
				rsWeightNum = new Number(3, i + 1, rsWeight, wcfFC);
			} else {
				rsWeightNum = new Number(3, i + 1, rsWeight, boldNumberFormate);
			}

			sheet.addCell(rsWeightNum);
			// 把创建的内容写入到输出流中，并关闭输出流
		}

		workbook.write();
		workbook.close();
		os.close();

	}

}
